﻿///#undef MySql
///#define MSSql
#define MySql

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

using System.Data.SqlClient;
using System.Text.RegularExpressions;

using MySql.Data.MySqlClient;

namespace ukeasbjWeb
{
    public partial class Register : WebList_SXD //System.Web.UI.Page
    {
        
        private static string static_sOrder_by_Leads;

        /// <summary>
        /// 生成 sql 查询语句的 order by 部分
        /// </summary>
        /// <returns>返回Order by 字符串，如果出错，返回一个空格。</returns>
        protected string Get_OrderBy_FromSession(string myOrder_by)
        {
            string sOrder_by_Leads = Convert.ToString(Session["Order_by_Register"]).Trim();
            if (sOrder_by_Leads != "")
                return "order by " + sOrder_by_Leads + " ";
            else
                return "order by " + myOrder_by + " ";

        }
        //private static string static_sOrder_by_Register;


        protected void Page_Load(object sender, EventArgs e)
        {
            // 操作权限的判断
            Common_SXD.Is_LongIn(Response, Session, Request);

            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_G_M = Convert.ToBoolean(Session["Is_G_M"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Marketing = Convert.ToBoolean(Session["Is_Marketing"]);

            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_Frontline_Agent = Convert.ToBoolean(Session["Is_Frontline_Agent"]);
            string Name_Email = Session["Email"].ToString().Trim().ToLower();

            if (!bIs_O_M && !bIs_Admin && !bIs_Receptionist && !bIs_Counsellor && !bIs_Frontline_Agent
                && !bIs_Marketing)
            { //只有地区经理、系统管理员、前台、顾问、市场专员和客服专员可以进入
                Response.Write("<h2 align='center'><font face='宋体' color='red'>");
                Response.Write("<br>对不起，请用其他身份登录！");
                Response.Write("</fong></h2>");
                Response.End();
            }


            ///////////// 读出网页调用时带入的 order by信息，设定 ASC DESC 后，存入session["Order_by_Leads"]
            ///存入 order by 到 session 之中
            /*
            static_sOrder_by_Leads = Request["Order_by_Leads"];
            string sLast_Order_by_Leads = Convert.ToString(Session["Order_by_Leads"]).Trim();
            if (static_sOrder_by_Leads == "" || static_sOrder_by_Leads == null) ///没有order by 信息输入
            {
                Session["Order_by_Leads"] = "";
            }
            else
            {
                if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "ASC")
                    Session["Order_by_Leads"] = static_sOrder_by_Leads + " ASC";
                else if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "DESC")
                    Session["Order_by_Leads"] = static_sOrder_by_Leads + " DESC";
                else
                    Session["Order_by_Leads"] = "";

            }
            */

            if (!IsPostBack)
            {
                Init_Term_Search();
                if (bIs_Admin || bIs_G_M /* Name_Email == "christine_du" ||
                   Name_Email == "judy_yao" || Name_Email == "lmz" */
                    )
                {
                    DDList_Office.Enabled = true;
                    DDList_Office.Visible = true;
                    Common_SXD.Init_Office(DDList_Office);
                    Common_SXD.Init_YearMonth_Start(Drop_YearMonth_Start);
                    ///Get_Search_Session(); 	 

                    string sOffice = DDList_Office.SelectedItem.Text;
                    Init_Table_Register(sOffice);

                }
                else
                {
                    DDList_Office.Visible = true;
                    Common_SXD.Init_Office(DDList_Office);
                    Common_SXD.Init_YearMonth_Start(Drop_YearMonth_Start);
                    ///Get_Search_Session(); 	 

                    string temp = Session["Office"].ToString().Trim();
                    Common_SXD.SetSelectedIndex(temp, DDList_Office);
                    DDList_Office.Enabled = false;
                    Init_Table_Register(temp);

                }
                

            }
        }

        /// <summary>
        /// 初始化，注册表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Init_Table_Register(string myOffice)
        {
            //  在此处放置用户代码以初始化页面
            //////////////////////////////////////////////////////
            string sSearchValue = TextBox_SearchValue.Text.Trim();

            string sDDList_Operator = DDList_Operator.SelectedItem.Text;
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Marketing = Convert.ToBoolean(Session["Is_Marketing"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_Frontline_Agent = Convert.ToBoolean(Session["Is_Frontline_Agent"]);

            /// 指定 Office_Leads_Selected 为操作人所属的 Office
            /// 
            Session["Office_Leads_Selected"] = myOffice.ToString().Trim();


                ///Off_Display_Controls_Statistics();

                /// 显示注册清单
                List_Register(myOffice);
            



        }

        private void List_Register(string myOffice)
        {
            string sSearchValue = TextBox_SearchValue.Text.Trim();

            string sDDList_Operator = DDList_Operator.SelectedItem.Text;
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Marketing = Convert.ToBoolean(Session["Is_Marketing"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_Frontline_Agent = Convert.ToBoolean(Session["Is_Frontline_Agent"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);

            string sOrder_by_Leads = Convert.ToString(Session["Order_by_Leads"]);

            DateTime dNow = DateTime.Now;

            Label_Total.Text = "";
            Date_Msg.Text = "一期款日期:";

            ///没有权限时的 select 语句
            string sSql = "select " + Top_MSSql("1") + " Series_No from Contract where Series_No like '-99' and ";

            int tDay = DateTime.Now.Day;
            int tMonth = DateTime.Now.Month;
            int tYear = DateTime.Now.Year - 1;

            string sDay_Minus1Year = Convert.ToString(tYear) + "-" + Convert.ToString(tMonth) + "-" + Convert.ToString(tDay);

            if ( sDDList_Operator == "课程1佣金" && (bIs_Admin || bIs_Accountant))
            {
                Session.Timeout = 118;
                if (CheckBox_Display_Detailed.Checked == true)
                { ///显示详细数据时
                    sSql = "select " + Top_MSSql(TextMax_Lines.Text) +
                        " Series_No, Office, Counsellor, Received_Date1_1_Degree_courses, College1_Degree_courses, Pinyi, Gender_Student, Date_Birth, Course1_Degree_courses, ";
                    sSql += "Start1_Degree_courses, Finish1_Degree_courses, Level1_Degree_courses, Tuition1_Degree_courses, ";
                    sSql += "UKEAS_ID, CONCAT(Destinations_OS, ':', Student_Type) as 'Student_Type', YearMonth_Start from Contract";
                }
                else
                {
#if MSSql
                    sSql = "select " + Top_MSSql(TextMax_Lines.Text) +
                        " Series_No, Office, UKEAS_ID, Destinations_OS + ':' + Student_Type as 'Student_Type', Student_Name, Pinyi, Gender_Student, Date_Birth, Counsellor, College1_Degree_courses, College2_Degree_courses, College_Pre_sessional, College_Language_School, Private_Co_accommodation_name from Contract";
                   
#elif MySql
                    sSql = "select " + Top_MSSql(TextMax_Lines.Text) +
                        " Series_No, Office, Counsellor, Received_Date1_1_Degree_courses, College1_Degree_courses, Pinyi, Gender_Student, Date_Birth, Course1_Degree_courses, ";
                    sSql += "";
                    sSql += "UKEAS_ID, CONCAT(Destinations_OS, ':', Student_Type) as 'Student_Type', YearMonth_Start from Contract";

#endif
                }
            }
            else if (bIs_O_M || bIs_Counsellor)
            {
                Session.Timeout = 118;
                if (CheckBox_Display_Detailed.Checked == true)
                { ///显示详细数据时
                    sSql = "select " + Top_MSSql(TextMax_Lines.Text) +
                        " Series_No, Office, UKEAS_ID, Student_Name, Pinyi, Gender_Student, Date_Birth, Counsellor, ";
                    sSql += "College1_Degree_courses, Course1_Degree_courses, Start1_Degree_courses, Finish1_Degree_courses, Level1_Degree_courses, Type1_Degree_courses, Tuition1_Degree_courses, Ref_No1_Degree_courses, Helicopter1_Degree_courses, Notes1_Degree_courses, ";
                    sSql += "College2_Degree_courses, Course2_Degree_courses, Start2_Degree_courses, Finish2_Degree_courses, Level2_Degree_courses, Type2_Degree_courses, Tuition2_Degree_courses, Ref_No2_Degree_courses, Helicopter2_Degree_courses, Notes2_Degree_courses, ";
                    sSql += "College_Pre_sessional, Course_Pre_sessional, Wks_Pre_sessional, Type_Pre_sessional, Start_Pre_sessional, Finish_Pre_sessional, Fee_Pre_sessional, Ref_No_Pre_sessional, Notes_Pre_sessional, ";
                    sSql += "College_Language_School, Course_Language_School, Wks_Language_School, Start_Language_School, Finish_Language_School, Fee_Language_School, Ref_No_Language_School, Notes_Language_School, ";
                    sSql += "Private_Co_accommodation_name, Residence_Name_Co_accommodation, Wks_Co_accommodation, Start_Co_accommodation, Finish_Co_accommodation, Fee_Co_accommodation, Ref_No_Co_accommodation, Notes_Co_accommodation, Fee_INTO_Insurance from Contract";
                }
                else
                {
#if MSSql
                    sSql = "select " + Top_MSSql(TextMax_Lines.Text) +
                        " Series_No, Office, UKEAS_ID, Destinations_OS + ':' + Student_Type as 'Student_Type', Student_Name, Pinyi, Gender_Student, Date_Birth, Counsellor, College1_Degree_courses, College2_Degree_courses, College_Pre_sessional, College_Language_School, Private_Co_accommodation_name from Contract";
                   
#elif MySql
                    sSql = "select " + Top_MSSql(TextMax_Lines.Text) +
                        " Series_No, Office, UKEAS_ID, CONCAT(Destinations_OS, ':', Student_Type) as 'Student_Type', Student_Name, Pinyi, Gender_Student, Date_Birth, Counsellor, YearMonth_Start, College1_Degree_courses, College2_Degree_courses, College_Pre_sessional, College_Language_School, Private_Co_accommodation_name from Contract";
#endif
                }

            }
            else
            {
                sSql = "select Series_No from Contract ";
            }
            

            /// 读取过滤条件
            sSql += Filter_Search_Register(myOffice);

            /////排序方式设定开始
            
            if (sDDList_Operator == "添加" )
            {
                sSql += Get_OrderBy_FromSession("Office ASC, Student_Name, Date_Birth, Series_No DESC");
            }
            else if (sDDList_Operator == "显示注册清单" || sDDList_Operator == "修改")
            {
                sSql += Get_OrderBy_FromSession("Office ASC, Counsellor");
            }
            else if (sDDList_Operator == "课程1佣金" )
            {
                sSql += Get_OrderBy_FromSession("Office ASC, Counsellor");
            }/*
            else
            {
                sSql += Get_OrderBy_FromSession("Office , State_Customer DESC, Counsellor, Series_No_Lead DESC");
            }
            */
            /////排序方式结束
#if MSSql
            ///打开数据库，获取sqlReader变量
            SqlConnection sqlCnn = new SqlConnection();
            SqlDataReader sqlReader = Common_SXD.Open_MSSqlDataReader(sSql, sqlCnn, "打开数据库出错：", Response);
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;
            //清除表中的内容
            Table_Register.Rows.Clear();
#elif MySql
            sSql += Limit_MySql(TextMax_Lines.Text);

            ///打开数据库，获取sqlReader变量
            MySqlConnection MySqlconn = new MySqlConnection();
            MySqlDataReader MysqlReader = Common_SXD.Open_MySqlDataReader(sSql, MySqlconn, "打开数据库出错：", Response);
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = MysqlReader.FieldCount;
            //清除表中的内容
            Table_Register.Rows.Clear();
#endif

            /// 获取where 右侧， order 左侧的字符串，存入 strArray[0]中。
            string[] strArray = Regex.Split(sSql, "where");
            sSql = strArray[1];
            strArray = Regex.Split(sSql, "order");

#if MSSql
            //记录个数
            string str = Common_SXD.CHAR_MSSQL_PLUS1("select count(Series_No) from Contract",
                strArray[0], Response);

            Label_Total.Text = "共计:" + Convert.ToString(Convert.ToUInt64(str) - 1) + "条记录 ";
#elif MySql
            //记录个数
            string str = Common_SXD.CHAR_MySQL_PLUS1("select count(Series_No) from Contract",
                strArray[0], Response);

            Label_Total.Text = "共计:" + Convert.ToString(Convert.ToUInt64(str) - 1) + "条记录 ";
#endif
            /*
            // 读入第一个查询结果集， 即 Contract 中的数据
            int nCol = sqlReader.FieldCount;

            //清除表中的内容
            Table_Register.Rows.Clear();
            */
            //首先生成标题行
            {
                HtmlTableRow row = new HtmlTableRow();
                for (int i = 0; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
                    string temp;
                    if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "DEFAULT")
                    {
#if MSSql
                        temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
#elif MySql
                        temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
#endif
                    }
                    else
                    {
#if MSSql
                        if (static_sOrder_by_Leads == sqlReader.GetName(i))
                        {
                            temp = Common_SXD.English_Chinese(sqlReader.GetName(i));
                        }
                        else
                        {
                            temp = "<A href=\"./Leads.aspx?Order_by_Leads=" + sqlReader.GetName(i) + "\">";
                            temp += Common_SXD.English_Chinese(sqlReader.GetName(i));
                            temp += "</A>";
                        }
#elif MySql
                        if (static_sOrder_by_Leads == MysqlReader.GetName(i))
                        {
                            temp = Common_SXD.English_Chinese(MysqlReader.GetName(i));
                        }
                        else
                        {
                            temp = "<A href=\"./Leads.aspx?Order_by_Leads=" + MysqlReader.GetName(i) + "\">";
                            temp += Common_SXD.English_Chinese(MysqlReader.GetName(i));
                            temp += "</A>";
                        }

#endif
                    }

                    cell.Controls.Add(new LiteralControl(temp.Trim()));
                    row.Cells.Add(cell);
                }
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    cell.Controls.Add(new LiteralControl("修改/添加"));
                    row.Cells.Add(cell);
                }
                Table_Register.Rows.Add(row);
                row.BgColor = "Tan";
            }

            // 依次读出数据
            int j = 0;
#if MSSql
            while (sqlReader.Read())
#elif MySql
            while (MysqlReader.Read())
#endif
            {
                HtmlTableRow row = new HtmlTableRow();
#if MSSql
                String sNo_Series = sqlReader.GetString(0); 
#elif MySql
                String sNo_Series = MysqlReader.GetString(0);
#endif
                // Provide a different background color for alternating rows.
                if (j % 2 == 1)
                    row.BgColor = "Gainsboro";

                // Iterate through the cells of a row.
                {
                    HtmlTableCell cell = new HtmlTableCell();
                    /*
                    cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./LeadEdit.aspx?Series_No_Lead=ReadOnly:" +
                        sNo_Series_Lead.Trim() +
                        "',900,620);\">只读" +
                        sNo_Series_Lead.Trim() +
                        "</a>"));*/
                    cell.Controls.Add(new LiteralControl("<a>" +
                        sNo_Series.Trim() +
                        "</a>"));

                    row.Cells.Add(cell);
                }

                for (int i = 1; i < nCol; i++)
                {
                    // Create a new cell and add it to the Cells collection.
                    HtmlTableCell cell = new HtmlTableCell();
#if MSSql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(sqlReader.GetValue(i).ToString())));
#elif MySql
                    cell.Controls.Add(new LiteralControl(Common_SXD.TimeToYMD(MysqlReader.GetValue(i).ToString())));
#endif
                    row.Cells.Add(cell);
                }
                {
                    HtmlTableCell cell = new HtmlTableCell();

                    if (sDDList_Operator == "添加" && (bIs_O_M || bIs_Counsellor))
                    {
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./RegisterEdit.aspx?Series_No=AddedRegisterData:" +
                             sNo_Series.Trim() +
                             "',900,820);\">注册信息</a>"));
                        row.Cells.Add(cell);

                    }
                    else if (sDDList_Operator == "修改" && (bIs_O_M || bIs_Counsellor) )
                    {
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./RegisterEdit.aspx?Series_No=EditRegisterData:" +
                             sNo_Series.Trim() +
                             "',900,820);\">注册信息</a>"));
                        row.Cells.Add(cell);
                    }
                    else if (sDDList_Operator == "课程1佣金" && (bIs_Admin || bIs_Accountant ))
                    {
                        cell.Controls.Add(new LiteralControl("<a href=\"JavaScript:comOpen('./RegisterEdit.aspx?Series_No=EditRegisterData:" +
                             sNo_Series.Trim() +
                             "',900,820);\">注册信息</a>"));
                        row.Cells.Add(cell);
                    }
                    else if (sDDList_Operator == "显示注册清单" )
                    {
                        

                    }
                }

                Table_Register.Rows.Add(row);
                j++;
            }
#if MSSql
            sqlReader.Close(); // 关闭sqDataReader
            sqlCnn.Close(); // 关闭数据库连接
#elif MySql
            MysqlReader.Close(); // 关闭sqDataReader
            MySqlconn.Close(); // 关闭数据库连接
            
#endif
        }

        private void Init_Term_Search()
        {
            TextBox_Date_start_collector.Text = Common_SXD.startDate_CurrentMonth();
            TextBox_Date_end_collector.Text = Common_SXD.TimeToYMD(DateTime.Now.ToString());
            TextMax_Lines.Text = "86";

        }

        /// <summary>
        /// 设定客户表查询的过滤条件
        /// </summary>
        private string Filter_Search_Register(string myOffice)
        {
            string sSearchValue = TextBox_SearchValue.Text.Trim();

            string sStart_Date = TextBox_Date_start_collector.Text.Trim();
            string sEnd_Date = TextBox_Date_end_collector.Text.Trim();

            string sDDList_Operator = DDList_Operator.SelectedItem.Text;
            string sYearMonth_Start = Drop_YearMonth_Start.SelectedItem.Text;
            bool bIs_Admin = Convert.ToBoolean(Session["Is_Admin"]);
            bool bIs_O_M = Convert.ToBoolean(Session["Is_O_M"]);
            bool bIs_Marketing = Convert.ToBoolean(Session["Is_Marketing"]);
            bool bIs_Receptionist = Convert.ToBoolean(Session["Is_Receptionist"]);
            bool bIs_Counsellor = Convert.ToBoolean(Session["Is_Counsellor"]);
            bool bIs_Frontline_Agent = Convert.ToBoolean(Session["Is_Frontline_Agent"]);
            bool bIs_Accountant = Convert.ToBoolean(Session["Is_Accountant"]);

            string sOrder_by_Leads = Convert.ToString(Session["Order_by_Leads"]);

            DateTime dNow = DateTime.Now;

            string sSql_Search = "";
            /* if (bIs_Admin )
             {
             }
             else */
            int tDay = DateTime.Now.Day;
            int tMonth = DateTime.Now.Month;
            int tYear = DateTime.Now.Year - 1;

            string sDay_Minus1Year = Convert.ToString(tYear) + "-" + Convert.ToString(tMonth) + "-" + Convert.ToString(tDay);

            if (sDDList_Operator == "添加" && (bIs_O_M || bIs_Counsellor) )
            {
                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///如果不是地区经理（顾问），只能看本人的数据
                ///
                sSql_Search += Common_SXD.Filter_Counsellor(Session, Response);

                /// 还没有注册数据的记录
                sSql_Search += " ( College1_Degree_courses like '%a a a a a无%' or College1_Degree_courses like '%无%' or College1_Degree_courses is null  or College1_Degree_courses like '' ) and ";
                sSql_Search += " ( College2_Degree_courses like '%a a a a a无%' or College2_Degree_courses like '%无%' or College2_Degree_courses is null  or College2_Degree_courses like '' ) and ";
                sSql_Search += " ( College_Pre_sessional like '%a a a a a无%' or College_Pre_sessional like '%无%' or College_Pre_sessional is null  or College_Pre_sessional like '' ) and ";
                sSql_Search += " ( College_Language_School like '%a a a a a无%' or College_Language_School like '%无%' or College_Language_School is null  or College_Language_School like '' ) and ";
                sSql_Search += " ( Private_Co_accommodation_name like '%无%' or Private_Co_accommodation_name is null  or Private_Co_accommodation_name like '' ) and ";
                sSql_Search += " Date_First_Payment  >= '" + sStart_Date + "' and  Date_First_Payment  <= '" + sEnd_Date + "' and ";
                if (sYearMonth_Start != "无")
                    sSql_Search += " YearMonth_Start  like '%" + sYearMonth_Start + "%' and ";
                ///
                /// 
                
            }
            else if ((sDDList_Operator == "显示注册清单" || sDDList_Operator == "修改") && (bIs_O_M || bIs_Counsellor) )
            {

                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///如果不是地区经理（顾问），只能看本人的数据
                ///
                sSql_Search += Common_SXD.Filter_Counsellor(Session, Response);

                sSql_Search += " ( ( College1_Degree_courses not like '%a a a a a无%' and  College1_Degree_courses not like '%无%' and  College1_Degree_courses is not null and College1_Degree_courses not like '' ) or ";
                sSql_Search += " ( College2_Degree_courses not like '%a a a a a无%' and  College2_Degree_courses not like '%无%' and  College2_Degree_courses is not null and College2_Degree_courses not like '' ) or ";
                sSql_Search += " ( College_Pre_sessional not like '%a a a a a无%' and  College_Pre_sessional not like '%无%' and  College_Pre_sessional is not null and College_Pre_sessional not like '' ) or ";
                sSql_Search += " ( College_Language_School not like '%a a a a a无%' and  College_Language_School not like '%无%' and  College_Language_School is not null and College_Language_School not like '' ) or ";
                sSql_Search += " ( Private_Co_accommodation_name not like '%无%' and  Private_Co_accommodation_name is not null and Private_Co_accommodation_name not like '' ) ";
                sSql_Search += ") and ";
                sSql_Search += "  YearMonth_Start like '%" + sYearMonth_Start + "%'  and ";
                sSql_Search += " Date_First_Payment  >= '" + sStart_Date + "' and  Date_First_Payment  <= '" + sEnd_Date + "' and ";
                
            }
            else if ((sDDList_Operator == "课程1佣金") && (bIs_Admin || bIs_Accountant))
            {

                if (DDList_Office.Enabled)
                {
                    if (myOffice == "总部")
                        sSql_Search += " where ";
                    else
                        sSql_Search += " where ( Office = '" + myOffice + "') and ";
                }
                else
                    sSql_Search += " where ( Office = '" + Session["Office"].ToString().Trim() + "') and ";

                ///如果不是地区经理（顾问），只能看本人的数据
                ///
                sSql_Search += Common_SXD.Filter_Counsellor(Session, Response);

                sSql_Search += " ( ( College1_Degree_courses not like '%a a a a a无%' and  College1_Degree_courses not like '%无%' and  College1_Degree_courses is not null and College1_Degree_courses not like '' ) or ";
                sSql_Search += " ( College2_Degree_courses not like '%a a a a a无%' and  College2_Degree_courses not like '%无%' and  College2_Degree_courses is not null and College2_Degree_courses not like '' ) or ";
                sSql_Search += " ( College_Pre_sessional not like '%a a a a a无%' and  College_Pre_sessional not like '%无%' and  College_Pre_sessional is not null and College_Pre_sessional not like '' ) or ";
                sSql_Search += " ( College_Language_School not like '%a a a a a无%' and  College_Language_School not like '%无%' and  College_Language_School is not null and College_Language_School not like '' ) or ";
                sSql_Search += " ( Private_Co_accommodation_name not like '%无%' and  Private_Co_accommodation_name is not null and Private_Co_accommodation_name not like '' ) ";
                sSql_Search += ") and ";
                sSql_Search += "  YearMonth_Start like '%" + sYearMonth_Start + "%'  and ";
                sSql_Search += " Date_First_Payment  >= '" + sStart_Date + "' and  Date_First_Payment  <= '" + sEnd_Date + "' and ";
                sSql_Search += " Received_Date1_1_Degree_courses  > '1930-1-1' and Received_Date1_1_Degree_courses is not null and ";

            }
            else
            {
                sSql_Search += " where Series_No is null and ";
            }
            
            sSql_Search += "(  Student_Name  like '%" + sSearchValue + "%' ";
            sSql_Search += "or  Pinyi  like '%" + sSearchValue + "%' ";
            ///sSql_Search += "or  Series_No_Lead  like '%" + sSearchValue + "%' ";
            sSql_Search += "or  Series_No  like '%" + sSearchValue + "%' ";

            sSql_Search += "or Notes like '%" + sSearchValue + "%' ";
            
            ///sSql_Search += "or  Name_Inquirer  like '%" + sSearchValue + "%' ";
            ///sSql_Search += "or  Tel_Inquirer  like '%" + sSearchValue + "%' ";
            ///sSql_Search += "or  Email_Inquirer  like '%" + sSearchValue + "%' ";
            sSql_Search += "or UKEAS_ID like '%" + sSearchValue + "%' )";
            
            return sSql_Search;
        }


        protected void Bt_Search_Click(object sender, EventArgs e)
        {
            string sBClassID = Session["Office"].ToString().Trim();
            if (DDList_Office.Enabled)
                sBClassID = DDList_Office.SelectedItem.Text;
            if (RadioButton_OrderBy.SelectedValue.ToUpper().Trim() == "DEFAULT")
                static_sOrder_by_Leads = "";

            ///保存 session["Search_Leads"]
            /*
            string sSearch_Leads = sBClassID + ", ";
            sSearch_Leads += DDList_Operator.SelectedItem.Text.Trim() + ", ";
            ///sSearch_Leads += TextBox_Date_start_Register.Text.Trim() + ", ";
            ///sSearch_Leads += TextBox_Date_end_Register.Text.Trim() + ", ";
            sSearch_Leads += TextBox_SearchValue.Text.Trim() + ", ";
            sSearch_Leads += TextMax_Lines.Text.Trim() + ", ";
            sSearch_Leads += RadioButton_OrderBy.SelectedItem.Text.ToString().Trim() + ", ";

            Session["Search_Leads"] = sSearch_Leads;
            */
            Init_Table_Register(sBClassID);

        }
    }
}